﻿using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using System.Data;
using NLBS.Entity;

namespace NLBS.Data
{
    public partial class AccessDataProvider : DbProvider
    {
        #region comment

        public override int GetAllCommentCount()
        {
            throw new NotImplementedException();
        }

        public override IPagedCollection<Comment> GetAllCommentByPage(int startIndex, int pageSize)
        {
            string strPageSQL = string.Format(@"select top {0} tComm.*, 
            tLog.log_id,tLog.log_catID,tLog.log_title,tLog.log_authorID,tLog.log_mode  
            FROM  [blog_Article] tLog, [blog_Comment] tComm 
            WHERE tLog.log_id=tComm.log_id where log_id<=(select min(comm_id) 
            from (select top {1} comm_id from blog_Comment order by comm_id desc) as T)
              order by tComm.comm_postTime desc;", pageSize, startIndex);

            string strCountSQL="SELECT COUNT(log_id) AS TotalRecords from blog_Comment";
            DbCommand dbCommand = db.GetSqlStringCommand(strCountSQL);
            int count = (int)db.ExecuteScalar(dbCommand);

            IPagedCollection<Comment> lists = new PagedCollection<Comment>();
            lists.MaxItems = count;

            dbCommand = db.GetSqlStringCommand(strPageSQL);
            db.AddInParameter(dbCommand, "startIndex", DbType.Int32, startIndex);
            db.AddInParameter(dbCommand, "pageSize", DbType.Int32, pageSize);
            
            
            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                while (dataReader.Read())
                {
                    Comment article = CommonDataProvider.PopulateCommentFromIDataReader(dataReader);
                    lists.Add(article);
                }
            }
            return lists;
        }

        public override IList<Comment> GetCommentList()
        {

            DbCommand dbCommand = db.GetSqlStringCommand(@"SELECT tComm.*, 
            tLog.log_id as log_id,tLog.log_catID,tLog.log_title,tLog.log_authorID,tLog.log_mode 
            FROM [blog_Article] tLog, [blog_Comment] tComm WHERE tLog.log_id=tComm.log_id ORDER BY tComm.comm_postTime DESC");
            IDataReader dr = db.ExecuteReader(dbCommand);
            IList<Comment> lists = new List<Comment>();
            Comment comment = null;
            while (dr.Read())
            {
                comment = CommonDataProvider.PopulateCommentFromIDataReader(dr);
                comment.Article = PopulateCommentArticleFromIDataReader(dr);
                lists.Add(comment);
            }
            return lists;
        }

        public override IList<Comment> GetCommentListByArticleID(int articleID)
        {

            DbCommand dbCommand = db.GetSqlStringCommand(@"SELECT comm_id, log_id, comm_content, comm_authorID, 
            comm_author, comm_editMark, comm_hidden, comm_ubbFlags, 
            comm_postTime, comm_ip
            FROM      blog_Comment
            WHERE   (log_id = ?) ORDER BY comm_postTime DESC");
            db.AddInParameter(dbCommand, "articleID", DbType.Int32, articleID);
            IDataReader dr = db.ExecuteReader(dbCommand);
            IList<Comment> lists = new List<Comment>();
            Comment comment = null;
            while (dr.Read())
            {
                comment = CommonDataProvider.PopulateCommentFromIDataReader(dr);
                lists.Add(comment);
            }
            return lists;
        }

        public override IList<Comment> GetCommentListByUserID(int userID)
        {

            DbCommand dbCommand = db.GetSqlStringCommand(@"SELECT  * FROM blog_Comment
            WHERE   (comm_authorID = ?) ORDER BY comm_postTime DESC");
            db.AddInParameter(dbCommand, "userID", DbType.Int32, userID);
            IDataReader dr = db.ExecuteReader(dbCommand);
            IList<Comment> lists = new List<Comment>();
            Comment comment = null;
            while (dr.Read())
            {
                comment = CommonDataProvider.PopulateCommentFromIDataReader(dr);
                lists.Add(comment);
            }
            return lists;
        }

        public override Comment GetCommentByID(int commID)
        {

            DbCommand dbCommand = db.GetSqlStringCommand(@"SELECT  * FROM blog_Comment
            WHERE   (comm_id = ?)");
            db.AddInParameter(dbCommand, "commID", DbType.Int32, commID);
            IDataReader dr = db.ExecuteReader(dbCommand);
            IList<Comment> lists = new List<Comment>();
            Comment comment = null;
            if (dr.Read())
            {
                comment = CommonDataProvider.PopulateCommentFromIDataReader(dr);
            }
            return comment;
        }

        public override int InsertComment(Comment comm)
        {
            using (DbConnection connection = db.CreateConnection())
            {
                connection.Open();
                DbTransaction transaction = connection.BeginTransaction();
                try
                {
                    DbCommand dbCommand = db.GetSqlStringCommand(@"INSERT INTO `blog_Comment` 
           (`comm_content`, `comm_authorID`, `comm_author`, 
           `comm_editMark`, `comm_hidden`, `comm_ubbFlags`, `comm_postTime`, `comm_ip`) 
            VALUES (?, ?, ?, ?, ?, ?, ?, ?)");

                    db.AddInParameter(dbCommand, "content", DbType.String, comm.Content);
                    db.AddInParameter(dbCommand, "authorID", DbType.Int32, comm.AuthorID);
                    db.AddInParameter(dbCommand, "author", DbType.String, comm.Author);
                    db.AddInParameter(dbCommand, "editMark", DbType.String, comm.EditMark);
                    db.AddInParameter(dbCommand, "hidden", DbType.Boolean, comm.Hidden);
                    db.AddInParameter(dbCommand, "ubbFlags", DbType.Int32, comm.UbbFlags);
                    db.AddInParameter(dbCommand, "postTime", DbType.DateTime, comm.PostTime);
                    db.AddInParameter(dbCommand, "ip", DbType.String, comm.IP);
                    db.ExecuteNonQuery(dbCommand, transaction);
                    dbCommand.Parameters.Clear();
                    dbCommand.CommandText = "SELECT @@IDENTITY";
                    int result = (int)db.ExecuteScalar(dbCommand, transaction);
                    transaction.Commit();
                    return result;
                }
                catch
                {
                    // Roll back the transaction. 
                    transaction.Rollback();
                }
                connection.Close();
            }
            return 0;
        }

        public override void UpdateComment(Comment comm)
        {
            
            DbCommand dbCommand = db.GetSqlStringCommand("UpdateComment");
            db.AddInParameter(dbCommand, "commID", DbType.Int32, comm.CommentID);
            db.AddInParameter(dbCommand, "logID", DbType.Int32, comm.LogID);
            db.AddInParameter(dbCommand, "content", DbType.String, comm.Content);
            db.AddInParameter(dbCommand, "authorID", DbType.Int32, comm.AuthorID);
            db.AddInParameter(dbCommand, "author", DbType.String, comm.Author);
            db.AddInParameter(dbCommand, "editMark", DbType.String, comm.EditMark);
            db.AddInParameter(dbCommand, "hidden", DbType.Boolean, comm.Hidden);
            db.AddInParameter(dbCommand, "ubbFlags", DbType.String, comm.UbbFlags);
            db.AddInParameter(dbCommand, "postTime", DbType.DateTime, comm.PostTime);
            db.AddInParameter(dbCommand, "ip", DbType.String, comm.IP);
            db.ExecuteNonQuery(dbCommand);
        }

        public override void DeleteComment(int commID)
        {
            Comment comm = GetCommentByID(commID);
            DbCommand dbCommand = db.GetSqlStringCommand(@"DELETE FROM [blog_Comment] WHERE [comm_id] = @commID");
            db.AddInParameter(dbCommand, "commID", DbType.Int32, commID);
            db.ExecuteNonQuery(dbCommand);
            
            ExecuteNonQuery(string.Format("UPDATE blog_Article SET log_commentCount =log_commentCount-1 WHERE log_id={0}", comm.LogID));

            ExecuteNonQuery("UPDATE blog_Settings SET set_value0 =set_value0-1 WHERE set_name='counterComment'");

            if (comm.AuthorID > 0)
            {
                ExecuteNonQuery(string.Format("UPDATE blog_User SET user_commentCount =user_commentCount-1  WHERE user_id={0}", comm.AuthorID));
            }
        }

        public override IPagedCollection<Comment> GetCommentBySearch(int startIndex, int pageSize, string prefix)
        {
            StringBuilder sb = new StringBuilder();
            string[] prefixArray = prefix.Split(new char[] { ' ' });

            for (int i = 0; i < prefixArray.Length; i++)
            {
                if (i > 0)
                {
                    sb.Append("AND ");
                }
                sb.AppendFormat("comm_content LIKE '%{0}%'", prefixArray[i]);
            }
            //comm_content
            DbCommand dbCommand = db.GetSqlStringCommand(string.Format(@"SELECT top {0} tComm.*, 
            tLog.log_id,tLog.log_catID,tLog.log_title,tLog.log_authorID,tLog.log_mode 
            FROM [blog_Article] tLog, [blog_Comment] tComm 
            WHERE comm_id<=(select min(comm_id) 
            from (select top {1} comm_id from blog_Comment order by comm_id desc) as T) AND {2} 
            and tLog.log_id=tComm.log_id ORDER BY tComm.comm_postTime DESC;
            SELECT COUNT(comm_id) AS TotalRecords from blog_Comment where {2}", startIndex, pageSize, sb.ToString()));
            IDataReader dr = db.ExecuteReader(dbCommand);
            IPagedCollection<Comment> lists = new PagedCollection<Comment>();
            Comment comment = null;
            while (dr.Read())
            {
                comment = CommonDataProvider.PopulateCommentFromIDataReader(dr);
                comment.Article = PopulateCommentArticleFromIDataReader(dr);
                lists.Add(comment);
            }
            lists.MaxItems = CommonDataProvider.PopulateTotalRecordsFromIDataReader(dr);
            return lists;
        }

        private Article PopulateCommentArticleFromIDataReader(IDataReader dr)
        {
            Article article = new Article();
            article.ArticleID = Convert.ToInt32(dr["log_id"]);
            article.CategoryID = Convert.ToInt32(dr["log_catID"]);
            article.Title = Convert.ToString(dr["log_title"]);
            article.AuthorID = Convert.ToInt32(dr["log_authorID"]);
            article.Mode = Convert.ToInt16(dr["log_mode"]);
            return article;
        }

        public override IList<Comment> GetTopComment(int intCount)
        {

            string sqlWHERE = string.Empty;
            if (intCount < 10)
            {
                intCount = 10;
            }

            List<string> intList = new List<string>();
            DbCommand dbHiddenCommand = db.GetSqlStringCommand("SELECT TOP " + intCount + " tComm.comm_id FROM [blog_Article] tLog, [blog_Comment] tComm, [blog_Category] tCat WHERE tLog.log_id=tComm.log_id AND tLog.log_catid=tCat.cat_id AND tLog.log_mode=1 AND tCat.cat_hidden=0 ORDER BY tComm.comm_postTime DESC");
            using (IDataReader dataReader = db.ExecuteReader(dbHiddenCommand))
            {
                while (dataReader.Read())
                {
                    //if (!intList.Contains(Convert.ToInt32(dataReader["log_id"])))
                    intList.Add(Convert.ToString(dataReader["comm_id"]));
                }
            }
            DbCommand dbAllCommand = db.GetSqlStringCommand("SELECT TOP " + intCount + " comm_id FROM [blog_Comment] ORDER BY comm_postTime DESC");
            using (IDataReader dataReader = db.ExecuteReader(dbAllCommand))
            {
                while (dataReader.Read())
                {
                    //if (!intList.Contains(Convert.ToInt32(dataReader["log_id"])))
                    intList.Add(Convert.ToString(dataReader["comm_id"]));
                }
            }
            IList<Comment> lists = new List<Comment>();
            if (intList.Count == 0)
            {
                return lists;
            }
            intList.Sort();
            string arrItem = intList[0];
            for (int i = 1; i < intList.Count; i++)
            {
                if (arrItem == intList[i])
                {
                    intList.RemoveAt(i);
                    i--;
                }
                else
                {
                    arrItem = intList[i];
                }
            }

            DbCommand dbCommand = db.GetSqlStringCommand(string.Format(@"SELECT tComm.*,tLog.*, tCat.*,tLog.log_id as log_id
FROM [blog_Article] tLog, [blog_Comment] tComm, [blog_Category] tCat 
WHERE tLog.log_id=tComm.log_id AND tLog.log_catid=tCat.cat_id AND tComm.comm_id IN ({0}) ORDER BY tComm.comm_postTime DESC", String.Join(",", intList.ToArray())));


            Comment comment = null;
            using (IDataReader dr = db.ExecuteReader(dbCommand))
            {
                while (dr.Read())
                {
                    comment = CommonDataProvider.PopulateCommentFromIDataReader(dr);
                    comment.Article.Title = Convert.ToString(dr["log_title"]);
                    comment.Article.Mode = Convert.ToInt32(dr["log_mode"]);
                    comment.Article.Category.Hidden = Convert.ToBoolean(dr["cat_hidden"]);
                    lists.Add(comment);
                }
            }
            return lists;
        }

        #endregion
    }
}
